package gen.tools.modUnit;

import gen.tools.Widgets;
import gen.tools.structures.ModUnit;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class CreateModUnit extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public CreateModUnit() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		/*	DB執行順序
		 * 
		 * 模組
		 * 封裝檔
		 * 變更申請
		 * 變更申請細項
		 * 變更申請依據需求		變更申請可操作人員
		 * 
		 * */
		
		final Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/text");
		
		final String subsysId = request.getParameter("subsysId");		//子系統ID
		final String empId = request.getParameter("empId");				//員工ID
	    final ModUnit modUnit = new Gson().fromJson( request.getParameter("modUnitJson"), ModUnit.class);
		
		String strSQL;
		String res ="Done";
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = wid.getConn();
			conn.setAutoCommit(false);	//為取得序號，先將提交關閉
			
			/****建立LOG，第一步就開始記錄程式的執行****/
			strSQL="INSERT INTO changelog (changeLogId,changeTypeId,creatorId,bDate) VALUES (null,1,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, empId);
			ps.setTimestamp(2, wid.cDT());
			ps.execute();
			strSQL="SELECT MAX(changeLogId) FROM changelog";
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			rs.next();
			final int clId4Create = rs.getInt(1);
			
			/****新增模組主檔****/	
			strSQL="INSERT INTO modUnits (subsysId,modUnitId,mrSVNpath,olSVNpath,modUnitTypeId,modUnitStatusId,comment,creatorId,cDT) VALUES (?,?,?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, subsysId);								//子系統
			ps.setString(2, modUnit.getModUnitId() );						//模組Id
			ps.setString(3, modUnit.getMrSvnPath() );					//SVN AP MR下的路徑
			ps.setString(4, modUnit.getOlSvnPath() );					//SVN AP MR下的路徑
			ps.setInt(5, modUnit.getModUnitTypeId() );						//模組型式
			ps.setInt(6, 0);										// 0 代表初始化
			ps.setString(7, modUnit.getComment() );						//註解
			ps.setString(8, empId );								//提交人員
			ps.setTimestamp(9, wid.cDT() );	//創建時間
			ps.execute();
			
			strSQL="SELECT MAX(modUnitId) FROM modUnits";
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			rs.next();
			final String modUnitId = modUnit.getModUnitId();
			
			/****創建變更申請****/
			strSQL = "INSERT INTO cramain (craId,subsysId,craStatusId,craTypeId,creatorId,cDT) VALUES (null,?,?,?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, subsysId);
			ps.setInt(2, 1);	//初始化申請
			ps.setInt(3, 2);	//2 表為 新增模組
			ps.setString(4, empId);
			ps.setTimestamp(5, wid.cDT());
			ps.execute();
			
			//保存新產生的變更申請序號，等等創建細項要用
			strSQL="SELECT MAX(craId) FROM cramain";
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			rs.next();
			final int craId = rs.getInt(1);		//變更申請序號ID
			
			/****創建變更申請細項****/
			strSQL = "INSERT INTO cradetail (craId,craDetId,modUnitId) VALUES (?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, craId);
			ps.setInt(2, 1);	//目前新增只會有一筆的變更申請細項
			ps.setString(3, modUnitId);
			ps.execute();
			
			
			/****變更申依據需求****/
			strSQL="INSERT INTO mpcrareq (craId,craDetId,reqId) VALUES (?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, craId);
			ps.setInt(2, 1); 	//目前新增只會有一筆的變更申請細項
			for(int i=0; i<modUnit.getReqs().size(); i++) {
				ps.setInt(3, modUnit.getReqs().get(i).getReqId());
				ps.execute();
			}
			
			/****變更申請可操作人員****/
			strSQL="INSERT INTO mpcraperformers (craId,craDetId,performerId,isRole) VALUES (?,?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, craId);
			ps.setInt(2, 1); 	//目前新增只會有一筆的變更申請細項
			for(int i=0; i<modUnit.getPerformers().size(); i++) {
				ps.setString(3, modUnit.getPerformers().get(i).getPerformerId());
				ps.setString(4, modUnit.getPerformers().get(i).isRole()?"t":"f");	//這個ID，是角色ID還是員工ID
				ps.execute();
			}
			
			
			/****將這次模組的新增，送至主管待審****/
			/****建立LOG，產生另一筆記錄動作****/
			strSQL="INSERT INTO changelog (changeLogId,changeTypeId,creatorId,bDate) VALUES (null,2,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, empId);
			ps.setTimestamp(2, wid.cDT());
			ps.execute();
			
			/****產生產生異動記錄劇本，以供日後查詢方便****/
			strSQL="INSERT INTO changelogscnario (changeLogId,dataType,masterId,detailId) VALUES (?,?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, clId4Create);
			ps.setInt(2, 1);		//主檔類型，1表示是cra，變更申請
			ps.setInt(3, craId);	//主檔ID
			ps.setInt(4, 1);		//細項ID
			ps.execute();

			/**** 將模組的狀態改為可異動 ****/
			strSQL = "UPDATE modUnits SET modUnitStatusId=1 WHERE modUnitId=?";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, modUnit.getModUnitId() );
			ps.execute();
			
			/****產生待辦事項****/
			strSQL="INSERT INTO todolist (subsysId,dataType,masterId,roleId,cDT,creatorId) VALUES (?,?,?,?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, subsysId);
			ps.setInt(2, 1);							//主檔類型，1表示這個ID是來自變更申請
			ps.setString(3, String.valueOf(craId) );	//主檔ID
			ps.setInt(4, 4);							//角色ID
			ps.setTimestamp(5, wid.cDT() );
			ps.setString(6, empId);
			ps.execute();
			
			/****將這筆變更申請目前的進度給修改****/
			strSQL = "UPDATE cramain SET craStatusId=? WHERE craId=?";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, 2);
			ps.setInt(2, craId);
			ps.execute();
			
			/****完成這次的異動****/
			strSQL = "UPDATE changelog SET eDate=? WHERE changeLogId=?";
			ps = conn.prepareStatement(strSQL);
			ps.setTimestamp(1, wid.cDT());
			ps.setInt(2, clId4Create);
			ps.execute();
			conn.commit();
		
		} catch(Exception e) {
			res = wid.errProcess(e);
			wid.errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		response.getWriter().write( res );
	}

}
